package com.vecspace.database.dao;

import java.io.Serializable;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Clob;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Node;

import com.vecspace.constant.DBType;
import com.vecspace.data.model.DataEntity;
import com.vecspace.data.model.IPagination;
import com.vecspace.data.model.Pagination;
import com.vecspace.data.model.FilterColumn;
import com.vecspace.database.model.DataTable;
import com.vecspace.database.model.IDataChangePublisher;
import com.vecspace.database.model.QueryResult;
import com.vecspace.database.model.QuerySQL;
import com.vecspace.exception.HException;
import com.vecspace.jsmodel.SqlParam;
import com.vecspace.utils.FileHelper;
import com.vecspace.utils.StringHelper;
import com.vecspace.utils.TJSON;
import com.vecspace.utils.XmlUtils;
import com.vecspace.utils.log.HLogger;

import comvecspace.data.model.sql.SqlCache;
import comvecspace.data.model.sql.SqlDataType;

/**
 * 
 * @version: 1.0
 * @author: wanghua
 */
public class BaseDao{
	@Resource(name="daoHelper")
	protected DaoHelper dao;
	 
	public DaoHelper getDao() {
		return dao;
	}
	public void setDao(DaoHelper dao) {
		this.dao = dao;
	}
	
	/**在定义具体的Dao类时，通过注入此属性来将数据的增、删、改信息放到消息队列中
	 * @author wanghua
	 */
	private IDataChangePublisher dataChangePublisher;

	public IDataChangePublisher getDataChangePublisher() {
		return dataChangePublisher;
	}

	public void setDataChangePublisher(IDataChangePublisher dataChangePublisher) {
		this.dataChangePublisher = dataChangePublisher;
	}
	
	/**
	 * 根据查询条件查询，一次列出所有满足结果的条件，不分页。
	 * @param clazz
	 * @param queryFields
	 * @param sortField
	 * @param sortOrder
	 * @return
	 * @throws Exception
	 * @author wanghua
	 */
	protected <T> List<T> queryObjectList(Class<T> clazz, List<FilterColumn> queryFields, String sortField, String sortOrder) throws Exception {
		Object[] params = null;// 查询参数对应的数值数组
		String whereClause="";
		if (queryFields != null && queryFields.size() > 0) {
			// 把查询条件列表转为查询条件sql语句和查询参数
			SqlParam sqlParam = FilterColumn.toSqlParam(queryFields);
			whereClause= sqlParam.getSql();// 查询条件
			List<Object> paramList = sqlParam.getParameter();// 查询参数List [,,]
			if (paramList != null && paramList.size() > 0){
				params = paramList.toArray();// 查询参数转为数组 [,,]
				String whereClause2 = whereClause.replace("?", "@@");
				int idx = 0;
				while(whereClause2.contains("@@")) {
					whereClause2 = whereClause2.replaceFirst("@@", "?"+idx);
					idx++;
				}
				// 组装查询条件
				whereClause = " where " + whereClause2;
			}
		}
		//在sql中增加排序参数
		String orderString = "";
		if (StringUtils.isNotEmpty(sortField)) {
			if(sortOrder == null) {
				sortOrder = "";
			}
			orderString = " order by " + sortField + " " + sortOrder;
		} else {
			orderString = "";
		}
		String hql = "from "+clazz.getSimpleName() + whereClause + orderString;// 整合sql
		return dao.queryObjectList(hql,params);
	}
	/**根据多个查询条件执行分页查询
	 * 功能描述：
	 * @param clazz
	 * @param queryFields 查询条件
	 * @param pageIndex 页数 从1开始
	 * @param pageSize 每页记录数，负数表示不分页
	 * @param sortField
	 * @param sortOrder
	 * @return
	 * @throws Exception
	 * @author wanghua
	 */
	protected <T> QueryResult<T> queryObjectList(Class<T> clazz, List<FilterColumn> queryFields,int pageIndex, int pageSize,String sortField, String sortOrder) throws Exception{		
		QueryResult<T> queryResult = queryObjectList(clazz.getSimpleName(), queryFields, pageIndex, pageSize, sortField, sortOrder);
		return queryResult;
	}
	
	/**
	 * 
	 * @param className
	 * @param filterColumn
	 * @param pageIndex
	 * @param pageSize 每页记录数，负数表示不分页
	 * @param sortField
	 * @param sortOrder
	 * @return
	 * @throws Exception 
	 * @author: wanghua
	 */
	protected <T> QueryResult<T> queryObjectList(String className, List<FilterColumn> filterColumn,int pageIndex, int pageSize,String sortField, String sortOrder) throws Exception{
		Object[] params = null;// 查询参数对应的数值数组

		if (filterColumn == null) {
			filterColumn = new ArrayList<FilterColumn>();
		}

		// 把查询条件列表转为查询条件sql语句和查询参数
		SqlParam sqlParam = FilterColumn.toHqlParam(filterColumn);
		String whereClause = sqlParam.getSql();// 查询条件
		List<Object> paramList = sqlParam.getParameter();// 查询参数List [,,]
		if (paramList != null && paramList.size() > 0){
			params = paramList.toArray();// 查询参数转为数组 [,,]
		}
			
		// 组装查询条件
		if (!StringUtils.isEmpty(whereClause)){
			whereClause = " where " + whereClause;
		}

		//在sql中增加排序参数
		String orderString = "";
		if (StringUtils.isNotEmpty(sortField)) {
			if(sortOrder == null) {
				sortOrder = "";
			}
			orderString = " order by " + sortField + " " + sortOrder;
		} else {
			orderString = "";
		}
		String hql = "from "+className + whereClause + orderString;// 整合sql

		QueryResult<T> queryResult = dao.queryObjectListByPage(hql, params, new Pagination(pageIndex, pageSize));
		
		return queryResult;
	}
	
	/**根据多个查询条件执行分页查询
	 * 功能描述：
	 * @param viewSql SQL视图，比如 select f1,f2,f3 from T1 或者 select t1.a,t1.b,t2.c,t2.d from t1 left outer join t2 on t1.x=t2.x 
	 * @param queryFields 查询条件
	 * @return
	 * @throws Exception 
	 * @author wanghua
	 */
	protected List<Map<String,Object>> queryDataRowsBySql(String viewSql, List<FilterColumn> queryFields) throws Exception{
		List<Map<String, Object>> dataList = this.queryDataRowsBySql(viewSql, queryFields, null, null);
		return dataList;
	}
		
	/**根据多个查询条件执行分页查询
	 * 功能描述：
	 * @param viewSql SQL视图，比如 select f1,f2,f3 from T1 或者 select t1.a,t1.b,t2.c,t2.d from t1 left outer join t2 on t1.x=t2.x 
	 * @param queryFields 查询条件
	 * @param sortField
	 * @param sortOrder
	 * @return
	 * @throws Exception
	 * @author wanghua
	 */
	protected List<Map<String,Object>> queryDataRowsBySql(String viewSql, List<FilterColumn> queryFields,String sortField, String sortOrder) throws Exception{
		//Object[] params = null;// 查询参数对应的数值数组
		return queryDataRowsBySql(viewSql, queryFields,"", sortField, sortOrder);
	}
	
	protected List<Map<String,Object>> queryDataRowsBySql(String viewSql, List<FilterColumn> queryFields, String groupField, String sortField, String sortOrder) throws Exception{
		Object[] params = null;// 查询参数对应的数值数组
		 
		if(queryFields == null) {
			queryFields = new ArrayList<FilterColumn>();
		}
		
		//把查询条件列表转为查询条件sql语句和查询参数
		SqlParam sqlParam = FilterColumn.toSqlParam(queryFields);
		String whereClause = sqlParam.getSql();//查询条件sql 
		List<Object> paramList = sqlParam.getParameter();//查询参数List [,,]
		if(paramList != null) {
			params = paramList.toArray();//查询参数转为数组 [,,]
		}
		
		String orderString = "";
		if(StringUtils.isNotEmpty(sortField)){
			if(sortOrder == null) {
				sortOrder = "";
			}
			orderString = "order by "+sortField +" "+ sortOrder;
		}

		String groupByString = "";
		if(groupField != null && groupField.trim().length()>0){
			groupByString = " group by "+groupField;
		}
		
		String sql = viewSql.trim();
		if(!StringUtils.isEmpty(whereClause)) {
			if(sql.contains("[and]")){
				sql = sql.replace("[and]", " and "+ whereClause);
			}else if(sql.contains("[where]")){
				sql = sql.replace("[where]", " where "+ whereClause);
			}else{
				sql = sql + " where " + whereClause;
			}
		}else{
			if(sql.contains("[and]")){
				sql = sql.replace("[and]", "");
			}else if(sql.contains("[where]")){
				sql = sql.replace("[where]", "");
			}
		}
		 
		sql = sql +" "+ groupByString + " " + orderString;//整合sql;

		List<Map<String,Object>> dataList = dao.queryDataRowMapList(sql, params);
		return dataList;
	}
	/**根据多个查询条件执行分页查询
	 * 功能描述：
	 * @param viewSql SQL视图，比如 select f1,f2,f3 from T1 或者 select t1.a,t1.b,t2.c,t2.d from t1 left outer join t2 on t1.x=t2.x 
	 * @param queryFields 查询条件
	 * @param pageIndex 页数 从1开始
	 * @param pageSize 页面的记录数， 负数表示不分页
	 * @param sortField
	 * @param sortOrder
	 * @return
	 * @throws Exception 
	 * @author wanghua
	 */
	protected QueryResult<Map<String,Object>> queryDataRowPageListBySql(String viewSql, List<FilterColumn> queryFields,int pageIndex, int pageSize,String sortField, String sortOrder) throws Exception{
		QueryResult<Map<String, Object>> result = queryDataRowPageListBySql(viewSql, queryFields, pageIndex, pageSize, "", sortField, sortOrder);
		return result;
	}

	/**根据多个查询条件执行分页查询
	 * 功能描述：
	 * @param viewSql SQL视图，比如 select f1,f2,f3 from T1 或者 select t1.a,t1.b,t2.c,t2.d from t1 left outer join t2 on t1.x=t2.x 
	 * @param queryFields 查询条件
	 * @param pageIndex 页数 从1开始
	 * @param pageSize 页面的记录数， 负数表示不分页
	 * @param sortField
	 * @param sortOrder
	 * @return
	 * @throws Exception 
	 * @author wanghua
	 */
	protected QueryResult<Map<String,Object>> queryDataRowPageListBySql(String viewSql, Object[] params, List<FilterColumn> queryFields,int pageIndex, int pageSize,String sortField, String sortOrder) throws Exception{
		QueryResult<Map<String, Object>> result = queryDataRowPageListBySql(viewSql, params, queryFields, pageIndex, pageSize, "", sortField, sortOrder);
		return result;
	}
	/**
	 * 
	 * @param viewSql
	 * @param queryFields
	 * @param pageIndex
	 * @param pageSize 页面的记录数， 负数表示不分页
	 * @param groupField
	 * @param sortField
	 * @param sortOrder
	 * @return
	 * @throws Exception 
	 * @author: wanghua
	 */
	protected QueryResult<Map<String,Object>> queryDataRowPageListBySql(String viewSql, List<FilterColumn> queryFields,int pageIndex, int pageSize, String groupField,String sortField, String sortOrder) throws Exception{
		return this.queryDataRowPageListBySql(viewSql, null, queryFields, pageIndex, pageSize, groupField, sortField, sortOrder);
	}
	
	/**
	 * 
	 * @param viewSql
	 * @param params viewSql中已经定义的参数对应的参数值，比如 viewSql：select * from t1 where t1.name in (select x from t2 where t2.id=?) [and] 
	 * @param filterColumns viewSql之外定义的查询条件
	 * @param pageIndex
	 * @param pageSize 页面的记录数， 负数表示不分页
	 * @param groupField
	 * @param sortField
	 * @param sortOrder
	 * @return
	 * @throws Exception 
	 * @author: wanghua
	 */
	protected QueryResult<Map<String,Object>> queryDataRowPageListBySql(String viewSql, Object[] params, List<FilterColumn> filterColumns, int pageIndex, int pageSize, 
			String groupField,String sortField, String sortOrder) throws Exception{
		 
		if(filterColumns == null) {
			filterColumns = new ArrayList<FilterColumn>();
		}
		
		//把查询条件列表转为查询条件sql语句和查询参数
		SqlParam sqlParam = FilterColumn.toSqlParam(filterColumns);
		String whereClause = sqlParam.getSql();//查询条件sql 
		List<Object> paramList = sqlParam.getParameter();//查询参数List [,,]
		if(paramList != null) {
			Object[] params2 = paramList.toArray();//查询参数转为数组 [,,]
			params = ArrayUtils.addAll(params, params2);
		}
		
		String orderString = "";
		if(StringUtils.isNotEmpty(sortField)){
			if(sortOrder == null) {
				sortOrder = "";
			}
			orderString = "order by "+sortField +" "+ sortOrder;
		}
		
		String groupByString = "";
		if(groupField != null && groupField.trim().length()>0){
			groupByString = " group by "+groupField;
		}
		
		String sql = viewSql.trim();
		if(!StringUtils.isEmpty(whereClause)) {
			if(sql.contains("[and]")){//viewsql中存在占位符的情况
				sql = sql.replace("[and]", " and "+ whereClause);
				sql = sql.replace("[where]", "");
			}else if(sql.contains("[where]")){
				sql = sql.replace("[where]", " where "+ whereClause);
			}else{
				sql = sql + " where " + whereClause;
			}
		}else{
			if(sql.contains("[and]")){
				sql = sql.replace("[and]", "");
			}
			if(sql.contains("[where]")){
				sql = sql.replace("[where]", "");
			}
		}
		 
		sql = sql +" "+ groupByString + " " + orderString;//整合sql;
		
		QueryResult<Map<String, Object>> queryResult = dao.queryDataRowMapListByPage(sql, params, new Pagination(pageIndex, pageSize));
		return queryResult;
	}
	
	/**根据sql语句查询DataEntity对象的列表。如果查询为空，则返回null。
	 * @param sql
	 * @param params
	 * @return
	 * @throws Exception
	 * @author wanghua
	 */
	protected DataEntity queryDataEntity(String sql, Object[] params) throws Exception{
		Map<String, Object> row = dao.queryDataRowMap(sql, params);
		if(row == null) {
			return null;
		}
		DataEntity entity = new DataEntity("");
		entity.addAllByColumnName(row);
		return entity;
	}
	
	/**根据sql语句查询DataEntity对象的列表。不分页。
	 * @param sql
	 * @param params
	 * @return
	 * @throws Exception 
	 * @author wanghua
	 */
	protected List<DataEntity> queryDataEntityList(String sql, Object[] params) throws Exception{
		List<Map<String, Object>> recordList = dao.queryDataRowMapList(sql, params);		 
		List<DataEntity> resultList = new ArrayList<>();
		for(Map<String,Object> row : recordList){
			DataEntity entity = new DataEntity("");
			entity.addAllByColumnName(row);
			resultList.add(entity);
		}		
		return resultList;
	}
	
	/**根据sql查询DataEntity对象的列表，分页查询。
	 * @param querySql
	 * @param queryFields
	 * @param pageIndex
	 * @param pageSize
	 * @param sortField
	 * @param sortOrder
	 * @return
	 * @throws Exception
	 * @author wanghua
	 */
	protected QueryResult<DataEntity> queryDataEntityPageList(String querySql, List<FilterColumn> queryFields, int pageIndex, int pageSize,String sortField, String sortOrder) throws Exception{
		if(pageIndex<1) {
			pageIndex = 1;
		}
		if(pageSize<1) {
			pageSize = 10;
		}
		String tableName = "";
		if(queryFields != null){
			for(FilterColumn queryField : queryFields){
				String columnName = queryField.getColumnName();
				columnName = StringHelper.toUnderscoreName(columnName);
				if(!columnName.contains(".")) {
					columnName = "t1."+ columnName;
				}
				queryField.setColumnName(columnName);
			}
		}
		sortField = StringHelper.toUnderscoreName(sortField);
		QueryResult<Map<String, Object>> queryResult = queryDataRowPageListBySql(querySql, queryFields, pageIndex, pageSize, sortField, sortOrder);
		QueryResult<DataEntity> resultPageModel = new QueryResult<DataEntity>(null, queryResult.getPagination());
		List<DataEntity> resultList = new ArrayList<DataEntity>();
		for(Map<String,Object> rowMap : queryResult.getEntities()){
			DataEntity entity = new DataEntity(tableName);
			entity.addAllByColumnName(rowMap);
			resultList.add(entity);
		}
		resultPageModel.setEntities(resultList);
		return resultPageModel;
	}
	 
	protected void onAddDataRow(String tableName, Object obj){
		if(dataChangePublisher == null) {
			return;
		}
		if(obj == null) {
			return;
		}
		
		if(tableName == null || tableName.length() == 0){
			HLogger.DEFAULT().error(tableName+"为空，请检查类"+obj.getClass().getName()+"Dao");
			return;
		}
		
		try {
			String jsonData = TJSON.toJSONString(obj);
			dataChangePublisher.addItem(tableName,  jsonData);
		} catch (Exception e) {
			HLogger.DEFAULT().error(e.getMessage());
			e.printStackTrace();
		}
	}
	
	protected void onDeleteDataRow(String tableName, Object obj){
		if(dataChangePublisher == null) {
			return;
		}
		if(obj == null) {
			return;
		}
		
		if(tableName == null || tableName.length() == 0){
			HLogger.DEFAULT().error(tableName+"为空，请检查类"+obj.getClass().getName()+"Dao");
			return;
		}
		
		try {
			String jsonData = TJSON.toJSONString(obj);
			dataChangePublisher.deleteItem(tableName,  jsonData);
		} catch (Exception e) {
			HLogger.DEFAULT().error(e.getMessage());
			e.printStackTrace();
		}
	}
	
	protected void onDeleteDataRow(String tableName, String id) throws Exception {
		if(dataChangePublisher == null) {
			return;
		}
		if(id == null || id.length() == 0) {
			return;
		}
		
		if(tableName == null || tableName.length() == 0){
			HLogger.DEFAULT().error(tableName+"为空，请检查调用函数");
			return;
		}
		
		try {
			dataChangePublisher.deleteItem(tableName, "{\"id\":\""+id+"\"}");
		} catch (Exception e) {
			HLogger.DEFAULT().error(e.getMessage());
			e.printStackTrace();
		}
	}

	protected void onDeleteDataRows(String tableName, String columnName, String columnValue) throws Exception {
		if(dataChangePublisher == null) {
			return;
		}
		if(tableName == null || tableName.length() == 0) {
			return;
		}
		try {
			dataChangePublisher.deleteItems(tableName, "{\""+columnName+"\":\""+columnValue+"\"}");
		} catch (Exception e) {
			HLogger.DEFAULT().error(e.getMessage());
			e.printStackTrace();
		}
	}
	/**
	 * 在定义具体的Dao类时，通过注入dataChangePublisher属性来将数据的增、删、改信息放到消息队列中
	 * @param obj 
	 * @author wanghua
	 */
	protected void onUpdateDataRow(String tableName, Object obj){
		if(dataChangePublisher == null) {
			return;
		}
		if(obj == null) {
			return;
		}
		
		if(tableName == null || tableName.length() == 0){
			HLogger.DEFAULT().error(tableName+"为空，请检查类"+obj.getClass().getName()+"Dao");
			return;
		}
		
		try {
			String jsonData = TJSON.toJSONString(obj);
			dataChangePublisher.updateItem(tableName, jsonData);
		} catch (Exception e) {
			HLogger.DEFAULT().error(e.getMessage());
			e.printStackTrace();
		}
	}
	
	protected String getSql(Class<?> clazz, String id) throws Exception{
		return DaoUtil.getSql(clazz, id);
	}
	
	/**读取 QuerySQL 对象
	 * 
	 * @param clazz
	 * @param id
	 * @return
	 * @throws Exception 
	 * @author wanghua
	 * @reviewer  
	 * @createTime 2021年9月30日 上午9:09:20
	 */
	protected QuerySQL getQuerySql(Class<?> clazz, String id) throws Exception{		
		return DaoUtil.getQuerySql(clazz, id);
	}
	
	protected void deleteObjects(List<?> list) throws Exception {
		dao.deleteObjects(list);
	}
	
	protected <T> T queryObject(Class<T> clazz, Serializable id) throws Exception {
		return dao.queryObject(clazz, id);
	}
	
	protected <T> T queryObject(String hql, Object[] params) throws Exception {
		return dao.queryObject(hql, params);
	}

	protected Object[] queryDataRowBySql(String sql, Object[] params) throws Exception {
		return dao.queryDataRow(sql, params);
	}
	
	protected void deleteObject(Class<?> clazz, Serializable id) throws Exception {
		dao.deleteObject(clazz, id);
		
	}
	
	protected void deleteObject(Object obj) throws Exception {
		dao.deleteObject(obj);
	}
	
	protected void updateObject(Object obj) throws Exception {
		dao.updateObject(obj);
	}
	
	protected void insertObject(Object obj) throws Exception {
		dao.insertObject(obj);
	}
		
	protected void saveOrUpdateObject(Object obj) throws Exception {
		dao.saveOrUpdateObject(obj);
	}
		
	protected <T> QueryResult<T> queryObjectListByPage(String hql, IPagination pagination) throws Exception {
		return dao.queryObjectListByPage(hql, pagination);
	}
	
	protected <T> List<T> queryObjectList(String hql) throws Exception {
		return dao.queryObjectList(hql);
	}
	
	protected <T> QueryResult<T> queryObjectListByPage(String hql, String[] paramNames, Object[] params, IPagination pagination) throws Exception {
		return dao.queryObjectListByPage(hql, paramNames, params, pagination);
	}
	
	protected <T> QueryResult<T> queryObjectListByPage(String hql, Object[] params, IPagination pagination) throws Exception {
		return dao.queryObjectListByPage(hql, params, pagination);
	}
	
	protected <T> QueryResult<T> queryObjectPageBySql(Class<T> clazz, String sql, Object[] params, IPagination pagination) throws Exception {
		return dao.queryObjectPageBySql(clazz, sql, params, pagination);
	}
	
	protected <T> List<T> queryObjectList(String hql, String[] paramNames, Object[] params) throws Exception {
		return dao.queryObjectList(hql, paramNames, params);
	}
	
	protected <T> List<T> queryObjectListBySql(Class<T> clazz, String sql, Object[] params) throws Exception {
		return dao.queryObjectListBySql(clazz, sql, params);
	}
	
	protected <T> T queryObjectBySql(Class<T> clazz, String sql, Object[] params) throws Exception {
		return dao.queryObjectBySql(clazz, sql, params);
	}
	
	protected <T> List<T> queryObjectList(String hql, Object[] params) throws Exception {
		return dao.queryObjectList(hql, params);
	}
	
	protected int[] batchUpdateDataRowBySql(String sql, List<Object[]> batchArgs) throws Exception {
		return dao.batchUpdateDataRowBySql(sql, batchArgs);
	}
	
	protected List<Long> batchUpdateReturnKey(String sql, List<Object[]> params) throws Exception {
		return dao.batchUpdateReturnKey(sql, params);
	}
	
	protected int updateDataRowBySql(String sql, Object[] params) throws Exception {
		return dao.updateDataRowBySql(sql, params);
	}
	
	protected int insertDataRowBySql(String sql, Object[] params) throws Exception {
		return dao.insertDataRowBySql(sql, params);
	}
	
	protected int deleteDataRowBySql(String sql, Object[] params) throws Exception {
		return dao.deleteDataRowBySql(sql, params);
	}
		
	protected QueryResult<Object[]> queryDataRowPageBySql(String sql, IPagination pagination) throws Exception {
		return dao.queryDataRowListByPage(sql, pagination);
	}
	
	protected List<Object[]> queryDataRowList(String s, Long pageLayoutId, String common, String sql) throws Exception {
		return dao.queryDataRowList(sql);
	}
	
	protected QueryResult<Object[]> queryDataRowListByPage(String Sql, Object[] params, IPagination pagination) throws Exception {
		return dao.queryDataRowListByPage(Sql, params, pagination);
	}
	
	protected List<Object[]> queryDataRowList(String sql, Object[] params) throws Exception {
		return dao.queryDataRowList(sql, params);
	}
	
	protected List<Map<String, Object>> queryDataRowMapList(String sql, Object[] params) throws Exception {
		return dao.queryDataRowMapList(sql, params);
	}
	
	protected Map<String, Object> queryDataRowMap(String sql, Object[] params) throws Exception {
		return dao.queryDataRowMap(sql, params);
	}
	
	protected QueryResult<Map<String, Object>> queryDataRowMapListByPage(String sql, Object[] params, IPagination pagination) throws Exception {
		return dao.queryDataRowMapListByPage(sql, params, pagination);
	}
	
	protected List<Map<String, Object>> queryDataRowMapList(String sql) throws Exception {
		return dao.queryDataRowMapList(sql);
	}
	
	protected QueryResult<Map<String, Object>> queryDataRowMapListByPage(String sql, IPagination pagination) throws Exception {
		return dao.queryDataRowMapListByPage(sql, pagination);
	}
	
	protected int callProcedure(String procedureName, List<String[]> params) throws Exception {
		return dao.callProcedure(procedureName, params);
	}
	
	protected Map<String, SqlDataType> queryColumnsType(String tableName) throws SQLException {
		return dao.queryColumnsType(tableName);
	}
	
	protected DataTable queryDataTable(String sql, Object[] params, int pageIndex, int pageSize) throws Exception {
		return dao.queryDataTable(sql, params, pageIndex, pageSize);
	}
	
	protected DataTable queryDataTable(String sql, Object[] params) throws Exception {
		return dao.queryDataTable(sql, params);
	}
	
	/**获取第一行第一列的值 */
	protected Object queryValue(String sql) throws Exception {
		return dao.queryValue(sql);
	}
	
	/**获取第一行第一列的值 */
	protected Object queryValue(String sql, Object[] params) throws Exception {
		return dao.queryValue(sql, params);
	}
	
	/**获取第一行第一列的值 */
	protected String queryString(String sql) throws Exception {
		return dao.queryString(sql);
	}
	
	/**获取第一行第一列的值 */
	protected String queryString(String sql, Object[] params) throws Exception {
		return dao.queryString(sql, params);
	}
	/**
	 * 获取第一行第一列的值如果为空，则返回0 
	 * @param sql
	 * @return
	 * @throws Exception 
	 * @author: wanghua
	 */
	protected int queryInt(String sql) throws Exception {
		return dao.queryInt(sql);
	}
	
	/**获取第一行第一列的值如果为空，则返回0
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * @throws Exception 
	 * @author: wanghua
	 */
	protected int queryInt(String sql, Object[] params) throws Exception {
		return dao.queryInt(sql, params);
	}
	
	/**
	 * 获取第一行第一列的值如果为空，则返回0 
	 * @param sql
	 * @return
	 * @throws Exception 
	 * @author: wanghua
	 */
	protected long queryLong(String sql) throws Exception {
		return dao.queryLong(sql);
	}
	
	/**
	 * 获取第一行第一列的值如果为空，则返回0 
	 * @param sql
	 * @param params
	 * @return
	 * @throws Exception 
	 * @author: wanghua
	 */
	protected long queryLong(String sql, Object[] params) throws Exception {
		return dao.queryLong(sql, params);
	}
	
	protected Clob createClob() throws Exception {
		return dao.createClob();
	}	
	
	/**Remove this instance from the session cache.*/
	@SuppressWarnings("rawtypes")
	public void evict(Object item){
		if(item == null)return;
		if(item instanceof List) {
			dao.evictList((List)item);
		}else {
			dao.evict(item);
		}
	}
	/**Remove the instance from the session cache.*/
	public void evictList(List<?> objList){
		if(objList == null || objList.size() == 0)return;
		dao.evictList(objList);
	}

	/**
	 * 类型转换，保持null转换后仍然为null
	 * @param obj
	 * @return
	 */
	public String asString(Object obj){
		if(obj == null) {
			return null;
		}
		return obj.toString();
	}
	/**
	 * 类型转换，保持null转换后仍然为null
	 * @param obj
	 * @return
	 */
	public Integer asInteger(Object obj) throws HException{
		if(obj == null) {
			return null;
		}
		else if(obj instanceof Integer) {
			return (Integer)obj;
		}
		else if(obj instanceof Long) {
			return ((Long)obj).intValue();
		}
		else if(obj instanceof BigInteger) {
			return ((BigInteger)obj).intValue();
		}
		else if(obj instanceof BigDecimal) {
			return ((BigDecimal)obj).intValue();
		}
		else if(obj instanceof Double) {
			return ((Double)obj).intValue();
		}
		else if(obj instanceof Float) {
			return ((Float)obj).intValue();
		}
		else {
			throw new HException(obj.getClass().getSimpleName()+"无法转为Integer类型");
		}
	}
	/**
	 * 类型转换，保持null转换后仍然为null
	 * @param obj
	 * @return
	 */
	public Long asLong(Object obj) throws HException{
		if(obj == null) {
			return null;
		}
		else if(obj instanceof Integer) {
			return ((Integer)obj).longValue();
		}
		else if(obj instanceof Long) {
			return (Long)obj;
		}
		else if(obj instanceof BigInteger) {
			return ((BigInteger)obj).longValue();
		}
		else if(obj instanceof BigDecimal) {
			return ((BigDecimal)obj).longValue();
		}
		else if(obj instanceof Double) {
			return ((Double)obj).longValue();
		}
		else if(obj instanceof Float) {
			return ((Float)obj).longValue();
		}
		else {
			throw new HException(obj.getClass().getSimpleName()+"无法转为Integer类型");
		}
	}
	/**
	 * 类型转换，保持null转换后仍然为null
	 * @param obj
	 * @return
	 */
	public Double asDouble(Object obj) throws HException{
		if(obj == null) {
			return null;
		}
		else if(obj instanceof Integer) {
			return ((Integer)obj).doubleValue();
		}
		else if(obj instanceof Long) {
			return ((Long)obj).doubleValue();
		}
		else if(obj instanceof BigInteger) {
			return ((BigInteger)obj).doubleValue();
		}
		else if(obj instanceof BigDecimal) {
			return ((BigDecimal)obj).doubleValue();
		}
		else if(obj instanceof Double) {
			return (Double)obj;
		}
		else if(obj instanceof Float) {
			return ((Float)obj).doubleValue();
		}
		else {
			throw new HException(obj.getClass().getSimpleName()+"无法转为Integer类型");
		}
	}
	/**
	 * 类型转换，保持null转换后仍然为null
	 * @param obj
	 * @return
	 */
	public Date asDate(Object obj) throws HException{
		if(obj == null) {
			return null;
		}
		else if(obj instanceof Date) {
			return (Date)obj;
		}
		else {
			throw new HException(obj.getClass().getSimpleName()+"无法转为Integer类型");
		}
	}
	
	/**更新单个值
	 * (调用此函数前，必须先判断columnName的合法性，columnName一定不能直接从前端传入不经判断就使用)
	 * @param tableName
	 * @param idColumn
	 * @param columnName
	 * @param id
	 * @param value
	 * @return 返回更新的记录数
	 * @throws Exception 
	 * @author: wanghua
	 */
	public int updateValue(String tableName, String idColumn, String columnName, Object id, Object value) throws Exception {
		String sql = "update " + tableName + " set " + columnName +"=? where " + idColumn + "=?";
		int rcount = this.updateDataRowBySql(sql, new Object[] {value, id});
		return rcount;
	}
	
	/**根据数据库类型，对null数字进行转换
	 * mysql if(isnull(batch_number),0,batch_number)
	 * oracle NVL(batch_number, 0)
	 * @param fieldName 字段名
	 * @param defaultValue 字段为null时的数值
	 * @return 
	 * @author: wanghua
	 */
	public String ifNullExpr(String fieldName, int defaultValue) {
		String sql = fieldName;
		if(DBType.isMySQLOfCurrent()) {
			sql = " if(isnull("+fieldName+"),"+defaultValue+","+fieldName+") ";
		}else if(DBType.isOracleOfCurrent()) {
			sql = " NVL("+fieldName+", "+defaultValue+") ";
		}else if(DBType.isPostgreSQLOfCurrent()) {  //nullif(var1,var2) 如果var1和var2相等则返回null，如果不相等则返回var1
			sql = " NULLIF("+fieldName+", "+defaultValue+") ";
		}
		return sql;
	}
}
